package sql;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import bean.Library;
import bean.Student;

public class SQLConnect {
	private final String driver = "com.mysql.jdbc.Driver";
	private final String URL = "jdbc:mysql://127.0.0.1:3306/yu";
	private final String username = "root";
	private final String password = "dys182182";
	public Connection con = null;
	
	public SQLConnect(){
		try{
			Class.forName(driver).newInstance();
			con = DriverManager.getConnection(URL,username,password);
		}catch (InstantiationException e) {
			e.printStackTrace();
		}catch (IllegalAccessException e) {
			e.printStackTrace();
		}catch (ClassNotFoundException e) {
			System.out.println("找不到数据库链接驱动失败");
			e.printStackTrace();
		}catch (SQLException e){
			e.printStackTrace();
		}
	}
	public void insertLibrary(Library library){
		String sqlStr = "insert into Library (book_id,book_name,borrow_time,return_time)"+
					"values ('"+library.getBookId()+"','"+library.getBookName()+"','"+
					library.getBorrowTime()+"','"+library.getreturnTime()+"')";
		System.out.println(sqlStr);
		try {
			PreparedStatement pre = con.prepareStatement(sqlStr);
			pre.execute();
		}catch (SQLException e){
			e.printStackTrace();
		}
	}
	
	public void insertStudent(Student student){
		String sqlStr = "insert into student" +
				" values ('" + student.getStudentNum()+ "','" + student.getName() + "'," +
				student.getSex() + "," + student.getGrade() + ",'" + student.getProfession() + "');";
		System.out.println(sqlStr);
		try {
			
			PreparedStatement pre = con.prepareStatement(sqlStr);
			pre.execute();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
	}
	
	public List<Student> selectAllStudent (){
		String sqlStr = "select * from student where 1=1";
		try {
			PreparedStatement ps = con.prepareStatement(sqlStr);
			ResultSet rs = ps.executeQuery();
			List<Student> studentList = new ArrayList<Student>();
			while(rs.next()){
				Student student = new Student();
				student.setStudentNum(rs.getString(1));
				student.setName(rs.getString(2));
				student.setSex(rs.getInt(3));
				student.setGrade(rs.getString(4));
				student.setProfession(rs.getString(5));
				studentList.add(student);
			}
			return studentList;
		} catch (SQLException e) {
			System.out.println("查无结果");
			e.printStackTrace();
			return null;
		}	
	}
	
	public List<Library> selectAllLibrary (){
		 String sqlStr = "select * from library where 1=1";
		 try{
			 PreparedStatement ps = con.prepareStatement(sqlStr);
			 ResultSet rs = ps.executeQuery();
			 List<Library> libraryList = new ArrayList<Library>();
			 while(rs.next()){
				 Library library = new Library();
				 library.setBookId(rs.getString(1));
				 library.setBookName(rs.getString(2));
				 library.setBorrowTime(rs.getString(3));
				 library.setReturnTime(rs.getString(4));
				 libraryList.add(library);
			 }
			 
			 return libraryList;
		 }catch (SQLException e){
			 System.out.println("查无结果");
			 e.printStackTrace();
			 return null;
		 }
	}
}
